﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Data;

namespace Vacation.DAL
{
    public class Option
    {
        public string Name { get; set; }
        public string Value { get; set; }
    }


    public class DataAccess
    {
        /// <summary>
        /// 检索数据生成下拉菜单，Code为代码,Name为显示名称
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="storedProcedureName">存储过程名</param>
        /// <param name="defaultValue">默认选择的值</param>
        /// <param name="hasAll" >是否包含% /全部选项</param>
        /// <returns></returns>
        public static SelectList getSelectList(string connectionString, string storedProcedureName, string defaultValue,bool hasAll)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand(storedProcedureName, sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            SqlDataReader sqlDataReader = null;

            SelectList selectList = null;
            
            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

                List<Option> lists=new List<Option>();

                Option list;
                if (hasAll)
                {
                    list = new Option { Name = "%", Value = "全部" };
                    lists.Add(list);
                }
                  while (sqlDataReader.Read())
                {
                  list=new Option{Name=sqlDataReader["Code"].ToString(),Value=sqlDataReader["Name"].ToString()};
                  lists.Add(list);
                }

                selectList = new SelectList(lists,"Name","Value",defaultValue);
            }
            catch (SqlException e)
            {

                    throw e;
            }
            finally
            {
                if (sqlDataReader != null)
                {

                    sqlDataReader.Close();
                }
            }
            return selectList;
        }
        /// <summary>
        /// 根据类型代码获取类型
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="storedProcedureName">存储过程</param>
        /// <param name="type">类型代码</param>
        /// <returns></returns>
        public static string getTypeName(string connectionString, string storedProcedureName,string type)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand(storedProcedureName, sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            string typeName = "";
            try
            {
                sqlCommand.Parameters.Add("@type", SqlDbType.NVarChar, 10).Value = type;
                sqlConnection.Open();
                object a=sqlCommand.ExecuteScalar();
                if(a!=null)
                    typeName=a.ToString();
            }
            catch (SqlException e)
            {

                throw e;
            }
            finally
            {
                sqlConnection.Close();
            }

            return typeName;
        }
    }
}